﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;
using SIPO.Library.DomainObject.Administration.UserManagement;

namespace SIPO.Library.DataAccess.Administration.Catalog
{
    public partial class PositionRepo : ISearchable<Position>
    {
        private List<string> listAtasan = new List<string>();

        private List<string> listAtasanScalar = new List<string>();
       

        public object SelectScalar(SQL.Function.Aggregate function, string column,string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM master.Position {1}", column,parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM master.Position {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM master.Position {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM master.Position A LEFT JOIN master.Department B ON A.DepartmentID = B.DeptID {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM master.Position {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM master.Position {1}", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }


        public object SelectScalarSendEmail(SQL.Function.Aggregate function, string column, string parameters,string status)
        {
            object _result = null;
           
            int countRows = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    StringBuilder sbQuery = new StringBuilder();
                  
                    sbQuery.AppendFormat("   SELECT  A.PositionID, A.RowStatus, A.PositionName, A.PositionDesc, A.DepartmentID, A.PositionSuperior, A.CreatedDate, A.CreatedBy, A.UpdatedDate, A.UpdatedBy,B.DepartmentDesc,(SELECT C.PositionName FROM master.Position C WHERE C.PositionID = ISNULL(A.PositionSuperior,0) ) SuperiorName,C.Email ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      master.Position A LEFT JOIN master.Department B ON A.DepartmentID = B.DeptID INNER JOIN master.recUser C ON A.PositionID = C.PositionID");
                    sbQuery.AppendFormat("      {0} ", parameters);
                    

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                string Ids = dr[Position.Fields.PositionID].ToString() + ",";

                                listAtasanScalar = new List<string>();
                                GetAtasanSclar(int.Parse(dr[Position.Fields.PositionID].ToString()));

                                foreach (string Id in listAtasanScalar)
                                {
                                    Ids += Id + ",";
                                }



                                Ids = "(" + Ids.Substring(0, Ids.Length - 1) + ")";

                                int countJob = 0;
                                int countJob2 = 0;
                                countJob = (int)SelectScalarJobByEmail(Ids);
                                if (countJob != 0)
                                    countJob2 = (int)SelectScalarJobByEmail(Ids + " AND ReviewBy IS  NULL");

                                if (status == "sudah" && countJob != 0 && countJob2 == 0)
                                {

                                    countRows++;
                                }
                                else if (status == "belum")
                                    {
                                        if (countJob != 0)
                                        {
                                            if (countJob2 != 0)
                                            {
                                                countRows++;
                                            }
                                        }
                                        else
                                            countRows++;
                                   
                                }
                                else if (status == "semua")
                                {
                                    countRows++;
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }

            _result = countRows;
            return _result;
        }

        public List<Position> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<Position> tt = new List<Position>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      A.PositionID, A.RowStatus, A.PositionName, A.PositionDesc, A.DepartmentID, A.PositionSuperior, A.CreatedDate, A.CreatedBy, A.UpdatedDate, A.UpdatedBy,B.DepartmentDesc,(SELECT C.PositionName FROM master.Position C WHERE C.PositionID = ISNULL(A.PositionSuperior,0) ) SuperiorName,C.Name ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      master.Position A LEFT JOIN master.Department B ON A.DepartmentID = B.DeptID LEFT JOIN master.Location C ON A.LocationID = C.LocationID ");
                    sbQuery.AppendFormat("      {0} ",parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                Position t = new Position(true);
                                t.PositionID = dr[Position.Fields.PositionID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.PositionID].ToString());
                                t.RowStatus = dr[Position.Fields.RowStatus] == DBNull.Value ? (bool?)null : bool.Parse(dr[Position.Fields.RowStatus].ToString());
                                t.PositionName = dr[Position.Fields.PositionName] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionName].ToString();
                                t.PositionDesc = dr[Position.Fields.PositionDesc] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionDesc].ToString();
                                t.DepartmentID = dr[Position.Fields.DepartmentID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.DepartmentID].ToString());
                                t.PositionSuperior = dr[Position.Fields.PositionSuperior] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.PositionSuperior].ToString());
                                t.CreatedDate = dr[Position.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[Position.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[Position.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[Position.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.UpdatedBy].ToString();

                                t.Department.DepartmentDesc = dr[Department.Fields.DepartmentDesc] == DBNull.Value ? string.Empty : dr[Department.Fields.DepartmentDesc].ToString();
                                t.SuperiorName = dr["SuperiorName"] == DBNull.Value ? string.Empty : dr["SuperiorName"].ToString();
                                t.Location.Name = dr[Location.Fields.Name] == DBNull.Value ? string.Empty : dr[Location.Fields.Name].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<Position> GetListByPositionID(int positionID)
        {
            List<Position> tt = new List<Position>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT PositionID, RowStatus, PositionName, PositionDesc, DepartmentID, PositionSuperior, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM master.Position WHERE PositionID=@PositionID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@PositionID", SqlDbType.Int).Value = positionID;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                Position t = new Position();
                                t.PositionID = dr[Position.Fields.PositionID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.PositionID].ToString());
                                t.RowStatus = dr[Position.Fields.RowStatus] == DBNull.Value ? (bool?)null : bool.Parse(dr[Position.Fields.RowStatus].ToString());
                                t.PositionName = dr[Position.Fields.PositionName] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionName].ToString();
                                t.PositionDesc = dr[Position.Fields.PositionDesc] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionDesc].ToString();
                                t.DepartmentID = dr[Position.Fields.DepartmentID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.DepartmentID].ToString());
                                t.PositionSuperior = dr[Position.Fields.PositionSuperior] == DBNull.Value ? (int?)null : int.Parse(dr[Position.Fields.PositionSuperior].ToString());
                                t.CreatedDate = dr[Position.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[Position.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[Position.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[Position.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.UpdatedBy].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<Position> GetListByDepartmentID(int departmentID)
        {
            List<Position> tt = new List<Position>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT PositionID, RowStatus, PositionName, PositionDesc, DepartmentID, PositionSuperior, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM master.Position WHERE DepartmentID=@DepartmentID ORDER BY PositionName";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = departmentID;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                Position t = new Position();
                                t.PositionID = dr[Position.Fields.PositionID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.PositionID].ToString());
                                t.RowStatus = dr[Position.Fields.RowStatus] == DBNull.Value ? (bool?)null : bool.Parse(dr[Position.Fields.RowStatus].ToString());
                                t.PositionName = dr[Position.Fields.PositionName] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionName].ToString();
                                t.PositionDesc = dr[Position.Fields.PositionDesc] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionDesc].ToString();
                                t.DepartmentID = dr[Position.Fields.DepartmentID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.DepartmentID].ToString());
                                t.PositionSuperior = dr[Position.Fields.PositionSuperior] == DBNull.Value ? (int?)null : int.Parse(dr[Position.Fields.PositionSuperior].ToString());
                                t.CreatedDate = dr[Position.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[Position.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[Position.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[Position.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.UpdatedBy].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<Position> GetListByPositionSuperior(int superiorID,int departmentID)
        {
            List<Position> tt = new List<Position>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT PositionID, RowStatus, PositionName, PositionDesc, DepartmentID, PositionSuperior, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM master.Position WHERE PositionSuperior=@PositionSuperior AND DepartmentID = @DepartmentID ORDER BY PositionName";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@PositionSuperior", SqlDbType.Int).Value = superiorID;
                    cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = departmentID;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                Position t = new Position();
                                t.PositionID = dr[Position.Fields.PositionID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.PositionID].ToString());
                                t.RowStatus = dr[Position.Fields.RowStatus] == DBNull.Value ? (bool?)null : bool.Parse(dr[Position.Fields.RowStatus].ToString());
                                t.PositionName = dr[Position.Fields.PositionName] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionName].ToString();
                                t.PositionDesc = dr[Position.Fields.PositionDesc] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionDesc].ToString();
                                t.DepartmentID = dr[Position.Fields.DepartmentID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.DepartmentID].ToString());
                                t.PositionSuperior = dr[Position.Fields.PositionSuperior] == DBNull.Value ? (int?)null : int.Parse(dr[Position.Fields.PositionSuperior].ToString());
                                t.CreatedDate = dr[Position.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[Position.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[Position.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[Position.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.UpdatedBy].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<Position> GetListByPositionSuperior(int superiorID)
        {
            List<Position> tt = new List<Position>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT PositionID, RowStatus, PositionName, PositionDesc, DepartmentID, PositionSuperior, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM master.Position WHERE PositionSuperior=@PositionSuperior  ORDER BY PositionName";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@PositionSuperior", SqlDbType.Int).Value = superiorID;
                  
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                Position t = new Position();
                                t.PositionID = dr[Position.Fields.PositionID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.PositionID].ToString());
                                t.RowStatus = dr[Position.Fields.RowStatus] == DBNull.Value ? (bool?)null : bool.Parse(dr[Position.Fields.RowStatus].ToString());
                                t.PositionName = dr[Position.Fields.PositionName] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionName].ToString();
                                t.PositionDesc = dr[Position.Fields.PositionDesc] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionDesc].ToString();
                                t.DepartmentID = dr[Position.Fields.DepartmentID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.DepartmentID].ToString());
                                t.PositionSuperior = dr[Position.Fields.PositionSuperior] == DBNull.Value ? (int?)null : int.Parse(dr[Position.Fields.PositionSuperior].ToString());
                                t.CreatedDate = dr[Position.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[Position.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[Position.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[Position.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.UpdatedBy].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<Position> GetListByDepartmentID(int departmentID,int positionID,int positionSuperiorID)
        {
            List<Position> tt = new List<Position>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"SELECT PositionID, RowStatus, PositionName, PositionDesc, DepartmentID, PositionSuperior, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM master.Position 
                                       WHERE DepartmentID=@DepartmentID
                                       AND  (PositionSuperior=@PositionSuperior OR PositionID=@PositionID)
                                       ORDER BY PositionName";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = departmentID;
                    cmd.Parameters.Add("@PositionID", SqlDbType.Int).Value = positionID;
                    cmd.Parameters.Add("@PositionSuperior", SqlDbType.Int).Value = positionSuperiorID;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                Position t = new Position();
                                t.PositionID = dr[Position.Fields.PositionID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.PositionID].ToString());
                                t.RowStatus = dr[Position.Fields.RowStatus] == DBNull.Value ? (bool?)null : bool.Parse(dr[Position.Fields.RowStatus].ToString());
                                t.PositionName = dr[Position.Fields.PositionName] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionName].ToString();
                                t.PositionDesc = dr[Position.Fields.PositionDesc] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionDesc].ToString();
                                t.DepartmentID = dr[Position.Fields.DepartmentID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.DepartmentID].ToString());
                                t.PositionSuperior = dr[Position.Fields.PositionSuperior] == DBNull.Value ? (int?)null : int.Parse(dr[Position.Fields.PositionSuperior].ToString());
                                t.CreatedDate = dr[Position.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[Position.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[Position.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[Position.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.UpdatedBy].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public DataTable GetDataTableReport(string DepartmentID)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    StringBuilder sbQuery = new StringBuilder();

                    sbQuery.AppendLine("SELECT   ROW_NUMBER() OVER(ORDER BY B.DepartmentDesc ASC,A.PositionName ASC) AS No");
                    sbQuery.AppendLine(",B.DepartmentDesc Departemen");
                    sbQuery.AppendLine(",A.PositionName Jabatan");
                    sbQuery.AppendLine(",(SELECT Z.PositionName FROM master.Position Z WHERE Z.PositionID=A.PositionSuperior) Atasan");
                    sbQuery.AppendLine("FROM  master.Position A INNER JOIN master.Department B");
                    sbQuery.AppendLine("ON A.DepartmentID = B.DeptID");
                    if (DepartmentID.Trim().Length>0)
                        sbQuery.AppendLine("WHERE A.DepartmentID = @DepartmentID");
                    
                    sbQuery.AppendLine("ORDER BY B.DepartmentDesc ASC,A.PositionName ASC");

                    cmd.CommandText = sbQuery.ToString();
                    if(DepartmentID.Trim().Length>0)
                        cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = DepartmentID;

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            return dt;

        }

        public List<Position> GetListSendEmail(int pageSize, int currentPage, string sortName, string sortOrder, string parameters,string status)
        {
           
            List<Position> tt = new List<Position>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      A.PositionID, A.RowStatus, A.PositionName, A.PositionDesc, A.DepartmentID, A.PositionSuperior, A.CreatedDate, A.CreatedBy, A.UpdatedDate, A.UpdatedBy,B.DepartmentDesc,(SELECT C.PositionName FROM master.Position C WHERE C.PositionID = ISNULL(A.PositionSuperior,0) ) SuperiorName,C.Email ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      master.Position A LEFT JOIN master.Department B ON A.DepartmentID = B.DeptID INNER JOIN master.recUser C ON A.PositionID = C.PositionID");
                    sbQuery.AppendFormat("      {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                string Ids = dr[Position.Fields.PositionID].ToString() + ",";

                                listAtasan = new List<string>();
                                GetAtasan(int.Parse(dr[Position.Fields.PositionID].ToString()));

                                foreach (string Id in listAtasan)
                                {
                                    Ids += Id + ",";
                                }

                               

                                Ids = "(" + Ids.Substring(0, Ids.Length - 1) + ")";

                                int countJob =0;
                                int countJob2 = 0;

                                countJob = (int)SelectScalarJobByEmail(Ids);

                                if (countJob != 0)
                                    countJob2 = (int)SelectScalarJobByEmail(Ids + " AND ReviewBy IS  NULL");

                                if (status == "sudah" && countJob!=0 && countJob2 == 0)
                                    {
                                            Position t = new Position(true);
                                            t.PositionID = dr[Position.Fields.PositionID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.PositionID].ToString());
                                            t.RowStatus = dr[Position.Fields.RowStatus] == DBNull.Value ? (bool?)null : bool.Parse(dr[Position.Fields.RowStatus].ToString());
                                            t.PositionName = dr[Position.Fields.PositionName] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionName].ToString();
                                            t.PositionDesc = dr[Position.Fields.PositionDesc] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionDesc].ToString();
                                            t.DepartmentID = dr[Position.Fields.DepartmentID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.DepartmentID].ToString());
                                            t.PositionSuperior = dr[Position.Fields.PositionSuperior] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.PositionSuperior].ToString());
                                            t.CreatedDate = dr[Position.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.CreatedDate].ToString());
                                            t.CreatedBy = dr[Position.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.CreatedBy].ToString();
                                            t.UpdatedDate = dr[Position.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.UpdatedDate].ToString());
                                            t.UpdatedBy = dr[Position.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.UpdatedBy].ToString();

                                            t.Department.DepartmentDesc = dr[Department.Fields.DepartmentDesc] == DBNull.Value ? string.Empty : dr[Department.Fields.DepartmentDesc].ToString();
                                            t.SuperiorName = dr["SuperiorName"] == DBNull.Value ? string.Empty : dr["SuperiorName"].ToString();

                                            t.recUser = new recUser();
                                            t.recUser.Email = dr[recUser.Fields.Email] == DBNull.Value ? string.Empty : dr[recUser.Fields.Email].ToString();

                                            tt.Add(t);
                                            t.Dispose();
                                        
                                    }
                                    else if (status == "belum")
                                    {
                                        if (countJob != 0)
                                        {
                                            if (countJob2 != 0)
                                            {
                                                Position t = new Position(true);
                                                t.PositionID = dr[Position.Fields.PositionID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.PositionID].ToString());
                                                t.RowStatus = dr[Position.Fields.RowStatus] == DBNull.Value ? (bool?)null : bool.Parse(dr[Position.Fields.RowStatus].ToString());
                                                t.PositionName = dr[Position.Fields.PositionName] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionName].ToString();
                                                t.PositionDesc = dr[Position.Fields.PositionDesc] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionDesc].ToString();
                                                t.DepartmentID = dr[Position.Fields.DepartmentID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.DepartmentID].ToString());
                                                t.PositionSuperior = dr[Position.Fields.PositionSuperior] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.PositionSuperior].ToString());
                                                t.CreatedDate = dr[Position.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.CreatedDate].ToString());
                                                t.CreatedBy = dr[Position.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.CreatedBy].ToString();
                                                t.UpdatedDate = dr[Position.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.UpdatedDate].ToString());
                                                t.UpdatedBy = dr[Position.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.UpdatedBy].ToString();

                                                t.Department.DepartmentDesc = dr[Department.Fields.DepartmentDesc] == DBNull.Value ? string.Empty : dr[Department.Fields.DepartmentDesc].ToString();
                                                t.SuperiorName = dr["SuperiorName"] == DBNull.Value ? string.Empty : dr["SuperiorName"].ToString();

                                                t.recUser = new recUser();
                                                t.recUser.Email = dr[recUser.Fields.Email] == DBNull.Value ? string.Empty : dr[recUser.Fields.Email].ToString();

                                                tt.Add(t);
                                                t.Dispose();
                                            }
                                        }
                                        else
                                        {
                                            Position t = new Position(true);
                                            t.PositionID = dr[Position.Fields.PositionID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.PositionID].ToString());
                                            t.RowStatus = dr[Position.Fields.RowStatus] == DBNull.Value ? (bool?)null : bool.Parse(dr[Position.Fields.RowStatus].ToString());
                                            t.PositionName = dr[Position.Fields.PositionName] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionName].ToString();
                                            t.PositionDesc = dr[Position.Fields.PositionDesc] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionDesc].ToString();
                                            t.DepartmentID = dr[Position.Fields.DepartmentID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.DepartmentID].ToString());
                                            t.PositionSuperior = dr[Position.Fields.PositionSuperior] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.PositionSuperior].ToString());
                                            t.CreatedDate = dr[Position.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.CreatedDate].ToString());
                                            t.CreatedBy = dr[Position.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.CreatedBy].ToString();
                                            t.UpdatedDate = dr[Position.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.UpdatedDate].ToString());
                                            t.UpdatedBy = dr[Position.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.UpdatedBy].ToString();

                                            t.Department.DepartmentDesc = dr[Department.Fields.DepartmentDesc] == DBNull.Value ? string.Empty : dr[Department.Fields.DepartmentDesc].ToString();
                                            t.SuperiorName = dr["SuperiorName"] == DBNull.Value ? string.Empty : dr["SuperiorName"].ToString();

                                            t.recUser = new recUser();
                                            t.recUser.Email = dr[recUser.Fields.Email] == DBNull.Value ? string.Empty : dr[recUser.Fields.Email].ToString();

                                            tt.Add(t);
                                            t.Dispose();
                                        }
                                        
                                    }
                                    else if(status=="semua")
                                    {
                                        Position t = new Position(true);
                                        t.PositionID = dr[Position.Fields.PositionID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.PositionID].ToString());
                                        t.RowStatus = dr[Position.Fields.RowStatus] == DBNull.Value ? (bool?)null : bool.Parse(dr[Position.Fields.RowStatus].ToString());
                                        t.PositionName = dr[Position.Fields.PositionName] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionName].ToString();
                                        t.PositionDesc = dr[Position.Fields.PositionDesc] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionDesc].ToString();
                                        t.DepartmentID = dr[Position.Fields.DepartmentID] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.DepartmentID].ToString());
                                        t.PositionSuperior = dr[Position.Fields.PositionSuperior] == DBNull.Value ? 0 : int.Parse(dr[Position.Fields.PositionSuperior].ToString());
                                        t.CreatedDate = dr[Position.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.CreatedDate].ToString());
                                        t.CreatedBy = dr[Position.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.CreatedBy].ToString();
                                        t.UpdatedDate = dr[Position.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Position.Fields.UpdatedDate].ToString());
                                        t.UpdatedBy = dr[Position.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[Position.Fields.UpdatedBy].ToString();

                                        t.Department.DepartmentDesc = dr[Department.Fields.DepartmentDesc] == DBNull.Value ? string.Empty : dr[Department.Fields.DepartmentDesc].ToString();
                                        t.SuperiorName = dr["SuperiorName"] == DBNull.Value ? string.Empty : dr["SuperiorName"].ToString();

                                        t.recUser = new recUser();
                                        t.recUser.Email = dr[recUser.Fields.Email] == DBNull.Value ? string.Empty : dr[recUser.Fields.Email].ToString();

                                        tt.Add(t);
                                        t.Dispose();
                                    }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        private void GetAtasan(int positionID)
        {
            List<Position> listPosition = GetListByPositionSuperior(positionID); //PositionDal.GetListByPositionSuperior(positionID);//, Convert.ToInt32(base.DepartmentID));
            foreach (Position data in listPosition)
            {
                listAtasan.Add(data.PositionID.ToString());
                GetAtasan(data.PositionID);
            }

        }

        private void GetAtasanSclar(int positionID)
        {
            List<Position> listPosition = GetListByPositionSuperior(positionID); //PositionDal.GetListByPositionSuperior(positionID);//, Convert.ToInt32(base.DepartmentID));
            foreach (Position data in listPosition)
            {
                listAtasanScalar.Add(data.PositionID.ToString());
                GetAtasan(data.PositionID);
            }

        }

        private object SelectScalarJobByEmail(string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();

                    sbQuery.AppendFormat("SELECT COUNT(*) FROM transactions.JobDescription WHERE PositionID IN {0}", parameters);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public bool UpdateWithoutImage(Position domain)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE master.Position SET RowStatus = @RowStatus, PositionName = @PositionName, PositionDesc = @PositionDesc, DepartmentID = @DepartmentID, PositionSuperior = @PositionSuperior, LocationID =@LocationID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE PositionID = @PositionID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@PositionID", SqlDbType.Int).Value = domain.PositionID;
                    cmd.Parameters.Add("@RowStatus", SqlDbType.Bit).Value = (object)domain.RowStatus ?? DBNull.Value;
                    cmd.Parameters.Add("@PositionName", SqlDbType.VarChar).Value = (object)domain.PositionName ?? DBNull.Value;
                    cmd.Parameters.Add("@PositionDesc", SqlDbType.VarChar).Value = (object)domain.PositionDesc ?? DBNull.Value;
                    cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = domain.DepartmentID;
                    cmd.Parameters.Add("@PositionSuperior", SqlDbType.Int).Value = (object)domain.PositionSuperior ?? DBNull.Value;
                    cmd.Parameters.Add("@LocationID", SqlDbType.Int).Value = (object)domain.LocationID ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;

                    try
                    {
                        conn.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return (result > 0);
        }
    }
}
